

/*------------------------------------------------------------------------------
	1. Housekeeping
------------------------------------------------------------------------------*/

	*Run housekeeping code
	qui do ".../replication_package/housekeeping.do"

	*Write log
	log using "$log_loc/table_1.log", text replace

	*Create Empty Tempfile to Store Regression Results
		tempfile table_1
		save `table_1', emptyok

/*------------------------------------------------------------------------------
	1. Clean Census Datasets
------------------------------------------------------------------------------*/

	*Import Census Datasets
		import excel using "$data/Census Count/nc-est2019-syasexn.xlsx", clear cellra(A6:M22)
			keep A K L M
			ren (A K L M) (age_num census_2017 census_2018 census_2019)

		tempfile census_data
		save `census_data', replace

		import excel using "$data/Census Count/nc-est2022-syasexn.xlsx", clear cellra(A6:E22)
			keep A C D
			ren (A C D ) (age_num census_2020 census_2021)

			merge 1:1 age_num using `census_data'
				assert _merge == 3
				drop _merge
	
			collapse (sum) census_*
			gen id = _n

	*Make the Counts Per Year
		reshape long census_, i(id) j(year)
			ren census_ census_ct
			drop id 
			isid year

		tempfile census_dta
		save `census_dta', replace


/*------------------------------------------------------------------------------
	2. Clean Main Datasets
------------------------------------------------------------------------------*/

	forvalues year = 2017/2021 {
		*Import Data
		use "$data/childrens`year'.dta", clear

			/*Restrict Sample*/
				*Keep f1095 filings
					rename state_insurance state
					fmerge m:1 state using "$data/statelist.dta"
						assert _merge != 2 //Confirm that all 51 states are in dataset
						drop if _merge != 3

				*Keep if age_num is within the range
					keep if age_num >= 0 & age_num <= 16

				*Keep if kid died after end of year or is currently alive
					keep if `year'*10000+1231<death_date|death_date==0

				*Keep if months covered is possible
					keep if num_cov_ins > 0 & num_cov_ins <= 12
	
			/*Generate variables for table*/
				*Generate outcome variable on claiming
					gen onreturn = file_inc != .

				*Generate a ct variable
					gen ct = 1

				*Generate variable for year
					gen year = `year'
			
/*------------------------------------------------------------------------------
	3. Carry Out Estimated Calculations
------------------------------------------------------------------------------*/
			/*Prepare backup data*/
				*Collapse to the by-age_num level		
					collapse (sum) ct onreturn, by(year)

				*Merge in Census Data
					merge 1:1 year using `census_dta'			
						assert _merge != 1
						keep if _merge == 3

				*Adjust for Claiming Limits
					replace onreturn = onreturn * 1.0017029

				*Create Percent Claimed variables
					gen perc_insured = ct/census_ct
					gen perc_claimed = onreturn/ct

				*Create Percent Claimed Upper Bound
					*onreturn/census_ct = LB, Assume everyone who did not have a 1095 is claimed (census_ct-ct/census_ct)
					gen perc_claimed_ub = (onreturn/ct) * (ct/census_ct) + (1 - ct/census_ct)

				*Create Percent Claimed Lower Bound
					gen perc_claimed_lb = (onreturn/ct) * (ct/census_ct)

				*Keep key outcomes
					keep perc_insured perc_claimed perc_claimed_ub perc_claimed_lb year

		*Save results as a tempfile
			tempfile results_`year'
			save `results_`year'', replace
	}

/*------------------------------------------------------------------------------
	7. Export the results as an Excel File
------------------------------------------------------------------------------*/
	*Import and append the results from 2017 to 2021
	forvalues year = 2017/2021 {
		use `results_`year'', clear		
		append using `table_1'

		tempfile table_1
		save `table_1', replace
	
	}
	
		*Organize before export
			sort year
			order year perc_insured perc_claimed perc_claimed_ub perc_claimed_lb

	*Export Results as an Excel File		
		export excel using "$output/table_backup_data.xlsx", first(var) sheet("base_table", replace)



